package com.work.demo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Service
public class DatabasePaginationToFile {

    private final DataSource dataSource;

    @Autowired
    public DatabasePaginationToFile(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public void paginateAndWrite(String tableName, String filename) throws SQLException, IOException {
        int pageSize = 100;
        int pageNum = 1;
        boolean hasMore = true;
        boolean isFirstPage = true;

        try (Connection conn = dataSource.getConnection();
             BufferedWriter writer = new BufferedWriter(new FileWriter(filename))) {

            while (hasMore) {
                String query = String.format("SELECT * FROM %s LIMIT ? OFFSET ?", tableName);
                try (PreparedStatement stmt = conn.prepareStatement(query)) {
                    stmt.setInt(1, pageSize);
                    stmt.setInt(2, (pageNum - 1) * pageSize);
                    ResultSet rs = stmt.executeQuery();

                    if (isFirstPage) {
                        List<String> columns = getColumnNames(rs);
                        writer.write(String.join(",", columns));  // Write header
                        writer.newLine();
                        isFirstPage = false;
                    }

                    while (rs.next()) {
                        writer.write(getRowData(rs, getColumnNames(rs)));
                        writer.newLine();
                    }

                    if (rs.isLast()) {
                        hasMore = false;
                    }

                    pageNum++;
                }
            }
        }
    }

    private static List<String> getColumnNames(ResultSet rs) throws SQLException {
        ResultSetMetaData metaData = rs.getMetaData();
        List<String> columns = new ArrayList<>();
        for (int i = 1; i <= metaData.getColumnCount(); i++) {
            columns.add(metaData.getColumnName(i));
        }
        return columns;
    }

    private static String getRowData(ResultSet rs, List<String> columns) throws SQLException {
        StringBuilder row = new StringBuilder();
        for (String column : columns) {
            row.append(rs.getString(column)).append(",");
        }
        return row.substring(0, row.length() - 1);  // Remove trailing comma
    }
}
